package com.houyi.sax;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by cdyizuoming on 2017/11/11.
 */
public class ExcelParseHander extends DefaultHandler {

    //共享字符串表
    private SharedStringsTable sharedStringsTable;
    //上一次的内容
    private String lastContents;
    private boolean nextIsString;
    private boolean isTElement;
    private int sheetNo = 0;
    private List<String> rowlist = new ArrayList<String>();
    //当前行
    private int curRow = 0;
    //当前列
    private int curCol = 0;


    /**
     * 遍历工作簿中所有的电子表格
     *
     * @throws Exception
     */
    public void execute(String filePath) throws Exception {
        OPCPackage pkg = OPCPackage.open(filePath);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        Iterator<InputStream> sheets = r.getSheetsData();
        while (sheets.hasNext()) {
            sheetNo++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst)
            throws SAXException {
        XMLReader parser = XMLReaderFactory
                .createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sharedStringsTable = sst;
        parser.setContentHandler(this);
        return parser;
    }

    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {

        if ("row".equals(name)) {
            this.curRow = Integer.valueOf(attributes.getValue("r"));
        }

        // c => 单元格
        if ("c".equals(name)) {
            // 如果下一个元素是 SST 的索引，则将nextIsString标记为true
            String cellType = attributes.getValue("t");
            if ("s".equals(cellType)) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }

        }
        //当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
        // 置空
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {

        // 根据SST的索引值的到单元格的真正要存储的字符串
        // 这时characters()方法可能会被调用多次
        /**
         * <c r="T10" s="22"><f>R10*S10</f><v>4700.2068399999998</v></c>
         * 这样的单元格 characters() 读取两次 "v".equals(name) 避免取到错误标签的值
         */

        if ("v".equals(name)) {
            if (nextIsString && StringUtils.isNotBlank(lastContents)) {
                try {
                    if (PatternUtils.patternCheck(lastContents, "intPattern")) {
                        int idx = Integer.parseInt(lastContents);
                        if (idx < sharedStringsTable.getItems().size()) {
                            lastContents = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx))
                                    .toString();
                        }
                    }
                } catch (Exception e) {
                    System.out.println(e);
                }
            }
            //t元素也包含字符串
            if (isTElement) {
                String value = (StringUtils.isNotBlank(lastContents)) ? lastContents.trim() : "";
                rowlist.add(curCol, value);
                curCol++;
                isTElement = false;
                // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
                // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            } else if ("v".equals(name)) {
                String value = (StringUtils.isNotBlank(lastContents)) ? lastContents.trim() : "";
                rowlist.add(curCol, value);
                curCol++;
            }
        }

        if (StringUtils.isBlank(lastContents) && "c".equals(name)) {
            rowlist.add(curCol, "");
            curCol++;
        }

        if (name.equals("row")) {
            //如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
            boolean flag = false;
            if (sheetNo == 1 && curRow != 7 && curRow != 6) {
                flag = true;
            } else if (sheetNo == 2 && curRow > 2) {
                flag = true;
            }
            if (flag) {
                rowlist.add(curCol, curRow + "");//最后一列行号
            }
            rowlist.clear();
            curCol = 0;
        }

    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        //得到单元格内容的值
        lastContents += new String(ch, start, length);
    }

}
